<?php

class Formstatus extends AppModel {

    var $name = 'Formstatus';
    var $displayField = 'dmdform_id';
    //The Associations below have been created with all possible keys, those that are not needed can be removed
    var $virtualFields = array(
        'display' => 'SELECT dmdforms.code from dmdforms where dmdforms.id=Formstatus.dmdform_id'
    );
    var $belongsTo = array(
        'Dmdformtype' => array(
            'className' => 'Dmdformtype',
            'foreignKey' => 'dmdforms_dmdformtype_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'Dmdformstatus' => array(
            'className' => 'Dmdformstatus',
            'foreignKey' => 'dmdformstatus_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'Dmdform' => array(
            'className' => 'Dmdform',
            'foreignKey' => 'dmdform_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'Demander' => array(
            'className' => 'Demander',
            'foreignKey' => 'demander_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'User' => array(
            'className' => 'User',
            'foreignKey' => 'user_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
    );

    function getDmdform($DmdId) {
        return $this->Dmdform->getDmdform($DmdId);
    }

    function getDmdforms() {
        return $this->Dmdform->getDmdforms();
    }

    function getUserMail($userId) {
        return $this->User->getUserMail($userId);
    }

    function getDemanderMail($formId) {

        $user = $this->find('list', array(
                    'conditions' => array(
                        'dmdform_id' => $formId
                    ),
                    'fields' => array('demander_id')
                ));
        $userId = $user[$formId];


        return $this->User->getUserMail($userId);
    }

    function getDemanderName($formId) {

        $user = $this->find('list', array(
                    'conditions' => array(
                        'dmdform_id' => $formId
                    ),
                    'fields' => array('demander_id')
                ));
        $userId = $user[$formId];


        return $this->User->getUser($userId);
    }

    function getItMailsForTools($dmdId) {
        return $this->Dmdform->getItMailsForTools($dmdId);
    }

    function getMailsForGroup($groupId) {
        return $this->User->getUserMailsForGroup($groupId);
    }

    function createAction($type, $dmdId, $by, $status) {
        $this->Dmdform->createAction($type, $dmdId, $by, $status);
    }

    function getStatus($dmdid) {
        $d = $this->find('list', array('conditions' => array('id' => $dmdid), 'fields' => array('dmdformstatus_id')));
        return $d[$dmdid];
    }

    function getStatusTitle($dmdid) {
        $d = $this->find('list', array('conditions' => array('id' => $dmdid), 'fields' => array('dmdformstatus_id')));
        return $this->Dmdformstatus->getTitle($d[$dmdid]);
    }

    function getCountForStatus($statusId) {
        $d = $this->find('count', array('conditions' => array('dmdformstatus_id' => $statusId)));
        return $d;
    }

    function getCountForStatusPerMonth($statusId, $Month) {
        $d = $this->find('count', array('conditions' => array('dmdformstatus_id' => $statusId, 'Month(Formstatus.created)' => $Month), 'group' => 'MONTH(Formstatus.created)'));
        return $d;
    }
    function getCountForStatusPerMonthAndYear($typeId, $Month, $Year) {
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdformstatus_id' => $typeId,
                        'Month(Formstatus.created)' => $Month,
                        'Year(Formstatus.created)' => $Year
                    ),
                    'group' => 'MONTH(Formstatus.created)')
        );
        return $d;
    }
    function getCountForStatusPerYear($typeId, $Year) {
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdformstatus_id' => $typeId,
                        'Year(Formstatus.created)' => $Year
                    ))
        );
        return $d;
    }
    function getCountForStatusPerYearAndCountry($typeId, $Year, $Country) {
        $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $Country), 'fields' => array('id')));
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdformstatus_id' => $typeId,
                        'Dmdform.company_id' => $comp,
                        'Year(Formstatus.created)' => $Year
                    ))
        );
        return $d;
    }
    function getCountForStatusPerMonthAndYearAndCountry($typeId, $Month, $Year, $Country) {
        $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $Country), 'fields' => array('id')));
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdformstatus_id' => $typeId,
                        'Dmdform.company_id' => $comp,
                        'Month(Formstatus.created)' => $Month,
                        'Year(Formstatus.created)' => $Year
                    ),
                    'group' => 'MONTH(Formstatus.created)')
        );
        return $d;
    }
    function getCountForTypes($typeId) {
        $d = $this->find('count', array('conditions' => array('dmdforms_dmdformtype_id' => $typeId)));
        return $d;
    }

    function getCountForTypesPerMonth($typeId, $Month) {
        $d = $this->find('count', array('conditions' => array('dmdforms_dmdformtype_id' => $typeId, 'Month(Formstatus.created)' => $Month), 'group' => 'MONTH(Formstatus.created)'));
        return $d;
    }

    function getCountForTypesPerYear($typeId, $Year) {
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdforms_dmdformtype_id' => $typeId,
                        'Year(Formstatus.created)' => $Year
                    ))
        );
        return $d;
    }

    function getCountForTypesPerYearAndCountry($typeId, $Year, $Country) {
        $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $Country), 'fields' => array('id')));
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdforms_dmdformtype_id' => $typeId,
                        'Dmdform.company_id' => $comp,
                        'Year(Formstatus.created)' => $Year
                    ))
        );
        return $d;
    }
    

    function getCountForTypesPerMonthAndYear($typeId, $Month, $Year) {
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdforms_dmdformtype_id' => $typeId,
                        'Month(Formstatus.created)' => $Month,
                        'Year(Formstatus.created)' => $Year
                    ),
                    'group' => 'MONTH(Formstatus.created)')
        );
        return $d;
    }

    function getCountForTypesPerMonthAndYearAndCountry($typeId, $Month, $Year, $Country) {
        $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $Country), 'fields' => array('id')));
        $d = $this->find('count', array(
                    'conditions' => array(
                        'dmdforms_dmdformtype_id' => $typeId,
                        'Dmdform.company_id' => $comp,
                        'Month(Formstatus.created)' => $Month,
                        'Year(Formstatus.created)' => $Year
                    ),
                    'group' => 'MONTH(Formstatus.created)')
        );
        return $d;
    }

    function getCountForTypesPerInterval($typeId, $date1, $date2=null) {

        $c['dmdforms_dmdformtype_id'] = $typeId;
        if (!empty($date2)) {
            $c['Formstatus.created BETWEEN ? AND ?'] = array($date1, $date2);
        } else {
            $c['Formstatus.created >='] = $date1;
        }

        $d = $this->find('count', array(
                    'conditions' => $c,
                    'group' => 'MONTH(Formstatus.created)')
        );
        return $d;
    }

    function getRisks($Y=null) {
        if (!$Y) {
            $Y = date('Y');
        }
        $t = $this->find('count');
        $n = $this->find('count', array('conditions' => array('has_risks' => 1, 'Year(Formstatus.created)' => $Y)));
        $r['nb'] = $n;
        $r['pourcent'] = round((($n / $t) * 100), 2) . " %";
        return $r;
    }

    function getRisksRejected($Y=null) {
        if (!$Y) {
            $Y = date('Y');
        }
        $t = $n = $this->find('count', array('conditions' => array('has_risks' => 1, 'Year(Formstatus.created)' => $Y)));
        $n = $this->find('count', array('conditions' => array('has_risks' => 1, 'rejected' => 1, 'Year(Formstatus.created)' => $Y)));
        $r['nb'] = $n;
        ($t == 0) ? $r['pourcent'] = '0 %' : $r['pourcent'] = round((($n / $t) * 100), 2) . " %";
        return $r;
    }

    function getRisksAcceptedCC($Y=null) {
        if (!$Y) {
            $Y = date('Y');
        }
        $t = $n = $this->find('count', array('conditions' => array('has_risks' => 1, 'Year(Formstatus.created)' => $Y)));
        $n = $this->find('count', array('conditions' => array('has_risks' => 1, 'accepted_cc' => 1, 'Year(Formstatus.created)' => $Y)));
        $r['nb'] = $n;

        ($t == 0) ? $r['pourcent'] = '0 %' : $r['pourcent'] = round((($n / $t) * 100), 2) . " %";
        return $r;
    }

    function getRisksKPI() {
        $r['Risky Demands'] = $this->getRisks();
        $r['Risky Demands: Rejected'] = $this->getRisksRejected();
        $r['Risky Demands: Accepted with CC'] = $this->getRisksAcceptedCC();
        $r_n['Risky Demands Year N-1'] = $this->getRisks(date('Y') - 1);
        $r_n['Risky Demands: Rejected Year N-1'] = $this->getRisksRejected(date('Y') - 1);
        $r_n['Risky Demands: Accepted with CC Year N-1'] = $this->getRisksAcceptedCC(date('Y') - 1);
        $risk['N'] = $r;
        $risk['N-1'] = $r_n;
        return $risk;
    }

    function getCreation($formId) {

        $b = $this->find('list', array('conditions' => array('id' => $formId), 'fields' => array('created')));
        $b = $b[$formId];
        return $b;
    }

    function getProcessAverage() {
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10),
                    'fields' => array('Avg(days_process) as m')
                ));
        $k['Average Leadtime'] = round($n[0][0]['m'], 2);
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10),
                    'fields' => array('Max(days_process) as m')
                ));
        $k['Highest Leadtime'] = $n[0][0]['m'];
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10),
                    'fields' => array('Min(days_process) as m')
                ));
        $k['Lowest Leadtime'] = $n[0][0]['m'];
        return $k;
    }

    function getProcessAverageForYear($Y=null) {
        if (!$Y) {
            $Y = date('Y');
        }
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y),
                    'fields' => array('Avg(days_process) as m')
                ));
        $k['Average Leadtime'] = round($n[0][0]['m'], 2);
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y),
                    'fields' => array('Max(days_process) as m')
                ));
        $k['Highest Leadtime'] = $n[0][0]['m'];
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y),
                    'fields' => array('Min(days_process) as m')
                ));
        $k['Lowest Leadtime'] = $n[0][0]['m'];
        return $k;
    }

    function getProcessAverageForYearAndMonth($Y=null, $M=null) {
        if (!$Y) {
            $Y = date('Y');
        }
        if (!$M) {
            $Y = date('M');
        }
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y, 'Month(Formstatus.created)' => $M),
                    'fields' => array('Avg(days_process) as m')
                ));
        $k['Average Leadtime'] = round($n[0][0]['m'], 2);
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y, 'Month(Formstatus.created)' => $M),
                    'fields' => array('Max(days_process) as m')
                ));
        $k['Highest Leadtime'] = $n[0][0]['m'];
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y, 'Month(Formstatus.created)' => $M),
                    'fields' => array('Min(days_process) as m')
                ));
        $k['Lowest Leadtime'] = $n[0][0]['m'];
        return $k;
    }
    /**
     * average time for year, month, country
     * 
     * @param int $Country
     * @param int $Y for year
     * @param int $M for month
     * 
     * @return mixed associative array
     */
    function getProcessAverageForYearAndMonthAndCountry($Country,$Y=null, $M=null) {
        if (!$Y) {
            $Y = date('Y');
        }
        if (!$M) {
            $M = date('M');
        }
        $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $Country), 'fields' => array('id')));
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('Dmdform.company_id' => $comp,'dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y, 'Month(Formstatus.created)' => $M),
                    'fields' => array('Avg(days_process) as m')
                ));
        $k['Average Leadtime'] = round($n[0][0]['m'], 2);
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('Dmdform.company_id' => $comp,'dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y, 'Month(Formstatus.created)' => $M),
                    'fields' => array('Max(days_process) as m')
                ));
        $k['Highest Leadtime'] = $n[0][0]['m'];
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('Dmdform.company_id' => $comp,'dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y, 'Month(Formstatus.created)' => $M),
                    'fields' => array('Min(days_process) as m')
                ));
        $k['Lowest Leadtime'] = $n[0][0]['m'];
        if(!($k['Lowest Leadtime']>0)) $k['Lowest Leadtime']='0';
        if(!($k['Highest Leadtime']>0)) $k['Highest Leadtime']='0';
        
        return $k;
    }
    function getProcessAverageForYearAndCountry($Country,$Y=null) {
        if (!$Y) {
            $Y = date('Y');
        }
        
        $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $Country), 'fields' => array('id')));
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('Dmdform.company_id' => $comp,'dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y),
                    'fields' => array('Avg(days_process) as m')
                ));
        $k['Average Leadtime'] = round($n[0][0]['m'], 2);
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('Dmdform.company_id' => $comp,'dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y),
                    'fields' => array('Max(days_process) as m')
                ));
        $k['Highest Leadtime'] = $n[0][0]['m'];
        $n = $this->find('all', array('recursibe' => -1, 'conditions' => array('Dmdform.company_id' => $comp,'dmdformstatus_id' => 10, 'Year(Formstatus.created)' => $Y),
                    'fields' => array('Min(days_process) as m')
                ));
        $k['Lowest Leadtime'] = $n[0][0]['m'];
        if(!($k['Lowest Leadtime']>0)) $k['Lowest Leadtime']='0';
        if(!($k['Highest Leadtime']>0)) $k['Highest Leadtime']='0';
        
        return $k;
    }

    function getCheckMail($id) {
        return $this->Dmdform->getCheckMail($id);
    }

    function getUsers() {
        return $this->Dmdform->getUsers();
    }

    function getUser($userId=null) {
        return $this->User->getUser($userId);
    }
    
    function getCountPerStatusAndCountryForYear($year){
        $countries=$this->Dmdform->Company->Country->find('list');
        $statuses=$this->Dmdformstatus->find('list');
        foreach ($countries as $key => $val) {
            $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $key), 'fields' => array('id')));
            foreach ($statuses as $statusKey => $statusVal) {
                $d[$key][$statusKey] = $this->find('count', array('conditions' => array('dmdformstatus_id' => $statusKey,'Dmdform.company_id' => $comp, 'Year(Formstatus.created)' => $year)));
            }
        }
        
    }
    function getCountPerStatusAndCountryForYearAndMonth($year,$Month){
        $countries=$this->Dmdform->Company->Country->find('list');
        $statuses=$this->Dmdformstatus->find('list');
        foreach ($countries as $key => $val) {
            $comp = $this->Dmdform->Company->find('list', array('conditions' => array('country_id' => $key), 'fields' => array('id')));
            foreach ($statuses as $statusKey => $statusVal) {
                $d[$key][$statusKey] = $this->find('count', array('conditions' => array('dmdformstatus_id' => $statusKey,'Dmdform.company_id' => $comp, 'Year(Formstatus.created)' => $year, 'Month(Formstatus.created)' => $Month)));
            }
        }
        
    }

}

?>